{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 作业1 关系数据库创建和数据查询"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "姓名：郑昱笙"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "学号：3180102760"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**作业目的：**了解数据库系统的发展历史与趋势，熟悉PostgreSQL数据库管理系统，掌握关系代数和关系数据库标准语言SQL，包括关系数据库和表的创建、数据导入和插入、完整性约束、数据查询、结果分析。\n",
    "\n",
    "**注意事项：**\n",
    "* 可以创建新的Cell用于测试，但问题回答和SQL语句写在相应的Cell中，并执行（Shift+Enter）\n",
    "* 看到 `In [*]:` ，意味着该Cell的SQL语句正在执行\n",
    "    * **如果运行时间过长：重新连接数据库，需要重新开始整个Kernel**\n",
    "    * 菜单\"Kernel >> Restart\", 重新执行SQL连接、数据库创建等前面的Cell \n",
    "* 注意:\n",
    "    * `%sql [SQL]` 是 _single line_ SQL queries\n",
    "    * `%%sql [SQL]` 是 _multi line_ SQL queries\n",
    "* **Jupyter Notebook对SQL语句的错误提示较弱，可以先在pgAdmin 4上执行，查看详细的错误信息**\n",
    "* 作业1总分50分，作业考察的题目后面标了具体分数，可以相互讨论思路，作业抄袭或雷同都要扣分\n",
    "* 作业1\\_学号\\_姓名.ipynb替换其中的学号和姓名，包含执行结果，提交到学在浙大，截止日期**2020.3.15**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. 阅读中国计算机学会通讯2014年第5期的[大数据存储渊源](http://www.cad.zju.edu.cn/home/ybtao/sdb/resources/CCCF2014.pdf)和2016年微信上的[诸神之战：计算机领域的固步自封与跨界战争](http://mp.weixin.qq.com/s/PSqJ_o3T_6vUww0V-bN4Gw)，根据文中内容回答以下问题。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1.1 关系型数据库公司RTI在技术创新上处于领先地位，但创业没有RSI成功，分析其主要原因有哪些？（2分）"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "1、从中情局这样的客户需求角度出发；\n",
    "2、强力的市场营销团队和可靠的技术后台；\n",
    "3、及时招揽人才\n",
    "4、成功让SQL成为了关系数据库的标准操作语言"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1.2 2009年的SIGMOD会议上，SAP的董事会主席，创始人之一，已经在大学任教的Hasso Plattner教授给了一个这样的报告：A Common Database Approach for OLTP and OLAP Using an In-Memory Column Database。分别举例说明生活中哪些应用属于OLTP和OLAP？（2分）"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "OLTP：联机事务处理\n",
    "举例：银行的一笔交易记录的增删改查\n",
    "OLAP：联机分析处理\n",
    "举例：处理商业智能、决策支持等重要的决策信息；"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. 关系代数"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "选课数据库\n",
    "* Student(<u>sid</u>, name, sex, age, dept)，表示学生学号，姓名，性别，年龄和院系\n",
    "* Course(<u>cid</u>, name, credit)，表示课程号，课程名和学分\n",
    "* SC(<u>sid</u>, <u>cid</u>, grade)，表示选课记录，sid和cid是Student和Course的外码\n",
    "\n",
    "基于第二章所学的关系代数，构造关系代数表达式，实现以下查询。数学符号可以通过[Latex Math Symbols](https://www.jianshu.com/p/9631408a5c69)表示：\n",
    "\n",
    "操作 | 数学符号 | Latex\n",
    "-|-|-\n",
    "选择 | $\\sigma$ | \\sigma\n",
    "投影 | $\\Pi$    | \\Pi\n",
    "笛卡尔积 | $\\times$ | \\times\n",
    "自然连接 | $\\bowtie$ | \\bowtie\n",
    "交集 | $\\cap$   | \\cap\n",
    "并集 | $\\cup$   | \\cup\n",
    "重命名| $\\rho$  | \\rho\n",
    "下标 | $X_{A,C}$| X_{A,C}\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2.0 查找'地理信息科学’专业选修过得课程名"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "答案：$\\Pi_{cname} (\\sigma_{dept='地理信息科学'}(Student) \\bowtie SC \\bowtie (\\rho_{cid, cname, credit}(Course)))$"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2.1 查找至少选修一门4学分及以上课程的学生学号（2分）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "答案：$\\Pi_{sid}(\\sigma_{credit>=4}(Course)\\bowtie SC \\bowtie Student )$"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2.2 查找学生'张三'或‘李四’选修过的课程名（2分）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "答案：$\\Pi_{cname}((\\sigma_{name='张三'}(Student) \\cup \\sigma_{name='李四'}(Student))\\bowtie SC \\bowtie (\\rho_{cid, cname, credit}(Course)))$"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2.3 查找学生'张三'和'李四'都选修过的课程名（2分）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "答案：$\\Pi_{cname}((\\sigma_{name='张三'}(Student)\\bowtie SC \\bowtie (\\rho_{cid, cname, credit}(Course))) \\cap (\\sigma_{name='李四'}(Student)\\bowtie SC \\bowtie (\\rho_{cid, cname, credit}(Course))))$"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2.4 查找所有学生成绩都大于等于80分的课程号，注意有的课程可能没有学生选修，如刚开设的课程 (2分)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " $\\Pi_{cid}(SC \\bowtie Course ) - \\Pi_{cid}(\\sigma_{grade<80}(SC) \\bowtie Course) $  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2.5 (附加题) 查找'地理空间数据库'(cid='06122870')成绩最高的学生学号，假设该课程成绩都不相同 (2分)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "答案：$ Pi_{ sid }( \\Pi{ grade }( \\sigma_{cid='06122870'}(Course) \\bowtie SC ) - \\Pi_{grade}( \\sigma_{ grade1 > grade }( \\rho_{grade1}( \\Pi_{grade}(\\sigma_{cid='06122870'}(Course) \\bowtie SC) ) \\times \\Pi_{grade}( \\sigma_{cid='06122870'}(Course) \\bowtie SC ) ) ) \\bowtie ( \\sigma_{cid='06122870'}(Course) \\bowtie SC ) )$"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. 公共自行车服务\n",
    "\n",
    "在PostgreSQL上创建站点、租车记录和天气关系数据库，导入相关数据，并构造相关数据查询语句。\n",
    "\n",
    "<img src=\"Figure 3.jpg\">\n",
    "\n",
    "####  3.1 公共自行车服务数据库的关系如下图所示：\n",
    "\n",
    "<img src=\"Figure 3.1.png\">\n",
    "\n",
    "基于上图创建Station、Trip和Weather关系。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "####  3.1.0 连接你所创建的数据库\n",
    "通过pgAdmin 4在PostgreSQL数据库中创建hw1数据库，并连接该数据库。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql postgresql://postgres:postgres@localhost/hw1\n",
    "\n",
    "SET statement_timeout = 0;\n",
    "SET lock_timeout = 0;\n",
    "SET client_encoding = 'utf-8';\n",
    "SET standard_conforming_strings = on;\n",
    "SET check_function_bodies = false;\n",
    "SET client_min_messages = warning;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.1.1 站点关系创建\n",
    "关系模式为station(<u>station_id</u>, station_name, lat, long, dock_count, city, installation_date, zip_code)。\n",
    "\n",
    "可以按照如下语句创建:\n",
    "drop table if exists station;\n",
    "CREATE TABLE station (\n",
    "station_id smallint not null primary key,\n",
    "station_name text,\n",
    "lat real,\n",
    "long real,\n",
    "dock_count smallint,\n",
    "city text,\n",
    "installation_date date,\n",
    "zip_code text\n",
    "); 其中dock_counts为站点的车位数，即站点自行车桩的数目。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "drop table if exists station;\n",
    "CREATE TABLE station (\n",
    "    station_id smallint not null primary key,\n",
    "    station_name text,\n",
    "    lat real,\n",
    "    long real,\n",
    "    dock_count smallint,\n",
    "    city text,\n",
    "    installation_date date,\n",
    "    zip_code text\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.1.2 租车关系创建（4分）\n",
    "关系模式为trip(<u>id</u>, duration, start_time, start_station_name, start_station_id, end_time, end_station_name, end_station_id, bike_id)， 其中，id为租车记录关系的主码，start_station_id和end_station_id为租车关系的外码。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "drop table if exists trip;\n",
    "CREATE TABLE trip (\n",
    "    id int not null primary key,\n",
    "    duration integer,\n",
    "    start_time timestamp,\n",
    "    start_station_name text,\n",
    "    start_station_id smallint references station(station_id),\n",
    "    end_time timestamp,\n",
    "    end_station_name text,\n",
    "    end_station_id smallint references station(station_id),\n",
    "    bike_id smallint\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.1.3 天气关系创建（2分）\n",
    "关系模式为weather(<u>date</u>, max_temp, mean_temp, min_temp, max_visibility_miles, mean_visibility_miles, min_visibility_miles, max_wind_speed_mph, mean_wind_speed_mph, max_gust_speed_mph, cloud_cover, envents, wind_dir_degrees, <u>zip_code</u>)，其中date和zip_code为天气的主码。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "drop table if exists weather;\n",
    "CREATE TABLE weather (\n",
    "    date date not null,\n",
    "    max_temp real,\n",
    "    mean_temp real,\n",
    "    min_temp real,\n",
    "    max_visibility_miles real,\n",
    "    mean_visibility_miles real,\n",
    "    min_visibility_miles real,\n",
    "    max_wind_speed_mph real,\n",
    "    mean_wind_speed_mph real,\n",
    "    max_gust_speed_mph real,\n",
    "    cloud_cover real,\n",
    "    events text,\n",
    "    wind_dir_degrees real,\n",
    "    zip_code text not null,\n",
    "    primary key(date,zip_code)\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.2 数据导入\n",
    "\n",
    "美国Bay Area五个城市收集的自行车公共服务数据，站点、租车记录和天气数据举例如下：\n",
    "\n",
    "<table border=\"1\">\n",
    "  <tr>\n",
    "    <th>station id</th>\n",
    "    <th>station name</th>\n",
    "    <th>latitude</th>\n",
    "    <th>longitude</th>\n",
    "    <th>dock count</th>\n",
    "    <th>city</th>\n",
    "    <th>installation date</th>\n",
    "    <th>zip code</th>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>2</td>\n",
    "    <td>San Jose Diridon Caltrain Station</td>\n",
    "    <td>37.3297</td>\n",
    "    <td>-121.902</td>\n",
    "    <td>27</td>\n",
    "    <td>San Jose</td>\n",
    "    <td>2013-08-06</td>\n",
    "    <td>95113</td>\n",
    "  </tr>\n",
    "</table>\n",
    "\n",
    "<table border=\"1\">\n",
    "  <tr>\n",
    "    <th>id</th>\n",
    "    <th>duration (sec)</th>\n",
    "    <th>start time</th>\n",
    "    <th>start station name</th>\n",
    "    <th>start station id</th>\n",
    "    <th>end time</th>\n",
    "    <th>end station name</th>\n",
    "    <th>end station id</th>\n",
    "    <th>bike id</th>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>5088</td>\n",
    "    <td>183</td>\n",
    "    <td>2013-08-29 22:08:00</td>\n",
    "    <td>Market at 4th</td>\n",
    "    <td>76</td>\n",
    "    <td>2013-08-29 22:12:00</td>\n",
    "    <td>Post at Kearney</td>\n",
    "    <td>47</td>\n",
    "    <td>309</td>\n",
    "  </tr>\n",
    "</table>\n",
    "\n",
    "<table border=\"1\">\n",
    "  <tr>\n",
    "    <th>date</th>\n",
    "    <th>max temp</th>\n",
    "    <th>mean temp</th>\n",
    "    <th>min temp</th>\n",
    "    <th>max visibility miles</th>\n",
    "    <th>mean visibility miles</th>\n",
    "    <th>min visibility miles</th>\n",
    "    <th>max wind speed mph</th>\n",
    "    <th>mean wind speed mph</th>\n",
    "    <th>max gust speed mph</th>\n",
    "    <th>cloud cover</th>\n",
    "    <th>envents</th>\n",
    "    <th>wind dir degrees</th>\n",
    "    <th>zip code</th>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>2013-08-29</td>\n",
    "    <td>74</td>\n",
    "    <td>68</td>\n",
    "    <td>61</td>\n",
    "    <td>10</td>\n",
    "    <td>10</td>\n",
    "    <td>10</td>\n",
    "    <td>23</td>\n",
    "    <td>11</td>\n",
    "    <td>28</td>\n",
    "    <td>4</td>\n",
    "    <td>NULL</td>\n",
    "    <td>286</td>\n",
    "    <td>94107</td>\n",
    "  </tr>\n",
    "</table>\n",
    "\n",
    "PostgreSQL可以通过[copy语句](https://www.postgresql.org/docs/current/static/sql-copy.html)批量导入数据，命令格式如下：\n",
    "    \n",
    "    copy [table name] from 'absolute file path of the data file' delimiter ‘,’; (建议使用绝对路径)\n",
    "    \n",
    "基于copy语句将给出的3个数据文件，导入到相应的关系中，文件中每行对应关系的一个元组（一行），属性是通过分隔符’,’隔离。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.2.1 站点关系数据[station](./station.txt)导入。(假设station.txt已从数据目录下拷贝到E盘）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "70 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql copy station from  'E://station.txt' delimiter ',';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.2.2 租车关系数据[trip](./trip.txt)导入。(假设trip.txt已从数据目录下拷贝到E盘）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "669958 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql copy trip from  'E://trip.txt' delimiter ',';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.2.3 天气关系数据[weather](./weather.txt)导入，未给出的数据默认为NULL，查看copy语句的帮助文档。（1分）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "3665 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql copy weather from  'E://weather.txt' delimiter ',' NULL '';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 通过select count(*) from station验证数据导入正确性（70, 669958, 3665）。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "1 rows affected.\n",
      " * postgresql://postgres:***@localhost/hw1\n",
      "1 rows affected.\n",
      " * postgresql://postgres:***@localhost/hw1\n",
      "1 rows affected.\n",
      "70 669958 3665\n"
     ]
    }
   ],
   "source": [
    "station_num = %sql select count(*) from station;\n",
    "trip_num    = %sql select count(*) from trip;\n",
    "weather_num = %sql select count(*) from weather;\n",
    "\n",
    "print(station_num[0][0], trip_num[0][0], weather_num[0][0]);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.3 数据插入（4分）\n",
    "\n",
    "假设最近一次的租车发生在2015年8月31号的23点26分，站点50，车编号为288，还车时间为2015年8月31号的23点39分，站点70，租借时长765秒。构造SQL语句将该租车记录插入到数据库中，id为trip数据库中id的最大值加1。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "0 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "delete from trip where start_time = '2015-8-31/23:26:00';\n",
    "insert into trip values(\n",
    "    (select max(id)+1 from trip),\n",
    "    765,\n",
    "    '2015-8-31/23:26:00',\n",
    "    (select station_name from station where station_id = 50),\n",
    "    50,\n",
    "    '2015-8-31/23:39:00',\n",
    "    (select station_name from station where station_id = 70),\n",
    "    70,\n",
    "    288\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.4 构造SQL语句实现以下数据查询与分析。\n",
    "\n",
    "每个查询使用一个SQL语句实现，除了题目要求外，不能使用with语句和视图，不能修改数据库内容和hardcode数值。建议首先使用with语句构建临时关系，实现题目要求的查询，然后将with语句通过子查询嵌入到select/from/where子句中。\n",
    "\n",
    "3.4.0 查询车位最多的站点。查询结果模式为(station_id, dock_count)。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>station_id</th>\n",
       "        <th>dock_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>61</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>77</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 27), (61, 27), (67, 27), (77, 27)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select station_id, dock_count\n",
    "from station\n",
    "where dock_count = (select max(dock_count) from station);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>station_id</th>\n",
       "        <th>dock_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>61</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>77</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 27), (61, 27), (67, 27), (77, 27)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select station_id, dock_count\n",
    "from station\n",
    "where dock_count >= all(select dock_count from station);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>station_id</th>\n",
       "        <th>dock_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>61</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>77</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 27), (61, 27), (67, 27), (77, 27)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select station_id, dock_count\n",
    "from station, (select max(dock_count) as max_dock_count from station) as mt\n",
    "where dock_count = max_dock_count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.4.1 查询每个城市的站点数量。查询结果模式为(city, number)，按站点数目降序排列，站点数目相同时，按城市名升序排列。（2分）\n",
    "\n",
    "空间关联：站点按所在城市进行**关联**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>city</th>\n",
       "        <th>number</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Jose</td>\n",
       "        <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mountain View</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Redwood City</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Palo Alto</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('San Francisco', 35),\n",
       " ('San Jose', 16),\n",
       " ('Mountain View', 7),\n",
       " ('Redwood City', 7),\n",
       " ('Palo Alto', 5)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select city,count(station_id) as number from station group by city order by number desc, city asc;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.4.2 查询距离最近的站点对。查询结果模式为(station_id_A, station_id_B, distance)，不能出现重复站点对，如(A, B, d)和(B, A, d)。（2分）\n",
    "\n",
    "**空间距离计算**是地理空间数据库的重点内容，将在后续课程学习PostGIS扩展函数和具体实现。现在提供PostgreSQL的PL/pgSQL语言函数dist，输入两个点的经纬度，计算弧度距离。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create or replace function dist(x1 float, y1 float, x2 float, y2 float) \n",
    "    returns float\n",
    "as $$\n",
    "begin\n",
    "    return sqrt((x2 - x1) * (x2 - x1) + (y2 - y1) * (y2 - y1));\n",
    "end;\n",
    "$$ language plpgsql;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "类似于PostgreSQL的其他函数，dist函数创建后，可以通过select dist(1.0, 2.0, 3.0, 4.0)使用。基于dist函数，构造SQL语句查询距离最近的站点对。\n",
    "\n",
    "空间关联：如果将每个站点和其距离最近站点连接**关联**，我们可以通过空间距离生成一个**静态**的站点网络"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>station1_id</th>\n",
       "        <th>station2_id</th>\n",
       "        <th>dist</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>69</td>\n",
       "        <td>70</td>\n",
       "        <td>0.00020655801957215704</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(69, 70, 0.00020655801957215704)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select s1.station_id as Station1_id, s2.station_id as Station2_id, dist(s1.lat,s1.long,s2.lat,s2.long) from (select * from station) as s1,(select * from station) as s2 where s1!= s2 order by dist limit 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.4.3 查询租车记录最多的前20个站点对。查询结果模式为(start_station_id, end_station_id, trip_count)，使用关键词limit获得前20个热门站点对。（4分）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "20 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>start_station_id</th>\n",
       "        <th>end_station_id</th>\n",
       "        <th>trip_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>69</td>\n",
       "        <td>65</td>\n",
       "        <td>6216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>60</td>\n",
       "        <td>6164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>65</td>\n",
       "        <td>70</td>\n",
       "        <td>5041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>61</td>\n",
       "        <td>50</td>\n",
       "        <td>4839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>61</td>\n",
       "        <td>4357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>74</td>\n",
       "        <td>4269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>51</td>\n",
       "        <td>70</td>\n",
       "        <td>3967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>61</td>\n",
       "        <td>3903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>64</td>\n",
       "        <td>77</td>\n",
       "        <td>3627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>50</td>\n",
       "        <td>3622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>70</td>\n",
       "        <td>3526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>55</td>\n",
       "        <td>3510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>65</td>\n",
       "        <td>69</td>\n",
       "        <td>3495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>70</td>\n",
       "        <td>3477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>69</td>\n",
       "        <td>39</td>\n",
       "        <td>3438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>77</td>\n",
       "        <td>64</td>\n",
       "        <td>3427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>50</td>\n",
       "        <td>3231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "        <td>70</td>\n",
       "        <td>3190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>60</td>\n",
       "        <td>3116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>70</td>\n",
       "        <td>3033</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(69, 65, 6216),\n",
       " (50, 60, 6164),\n",
       " (65, 70, 5041),\n",
       " (61, 50, 4839),\n",
       " (50, 61, 4357),\n",
       " (60, 74, 4269),\n",
       " (51, 70, 3967),\n",
       " (74, 61, 3903),\n",
       " (64, 77, 3627),\n",
       " (70, 50, 3622),\n",
       " (55, 70, 3526),\n",
       " (70, 55, 3510),\n",
       " (65, 69, 3495),\n",
       " (74, 70, 3477),\n",
       " (69, 39, 3438),\n",
       " (77, 64, 3427),\n",
       " (60, 50, 3231),\n",
       " (67, 70, 3190),\n",
       " (74, 60, 3116),\n",
       " (50, 70, 3033)]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select start_station_id, end_station_id, count(*) as trip_count from trip group by start_station_id, end_station_id order by trip_count desc limit 20;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "基于查询结果，分析站点之间的租车行为是否具有对称性，即站点A到站点B的租车量是否与站点B到站点A的租车辆相似？如果不相似，请从地理学角度宏观分析可能的原因。\n",
    "\n",
    "空间关联：如果某条租车记录从站点A到站点B，我们可以把站点A和站点B基于用户租车行为进行**关联**，生成一个**动态**的站点网络。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "Done.\n",
      "20 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>start_station_id</th>\n",
       "        <th>end_station_id</th>\n",
       "        <th>trip_count</th>\n",
       "        <th>start_station_id_1</th>\n",
       "        <th>end_station_id_1</th>\n",
       "        <th>trip_count_1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>69</td>\n",
       "        <td>65</td>\n",
       "        <td>6216</td>\n",
       "        <td>65</td>\n",
       "        <td>69</td>\n",
       "        <td>3495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>60</td>\n",
       "        <td>6164</td>\n",
       "        <td>60</td>\n",
       "        <td>50</td>\n",
       "        <td>3231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>65</td>\n",
       "        <td>70</td>\n",
       "        <td>5041</td>\n",
       "        <td>70</td>\n",
       "        <td>65</td>\n",
       "        <td>2320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>61</td>\n",
       "        <td>50</td>\n",
       "        <td>4839</td>\n",
       "        <td>50</td>\n",
       "        <td>61</td>\n",
       "        <td>4357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>61</td>\n",
       "        <td>4357</td>\n",
       "        <td>61</td>\n",
       "        <td>50</td>\n",
       "        <td>4839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>74</td>\n",
       "        <td>4269</td>\n",
       "        <td>74</td>\n",
       "        <td>60</td>\n",
       "        <td>3116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>51</td>\n",
       "        <td>70</td>\n",
       "        <td>3967</td>\n",
       "        <td>70</td>\n",
       "        <td>51</td>\n",
       "        <td>3031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>61</td>\n",
       "        <td>3903</td>\n",
       "        <td>61</td>\n",
       "        <td>74</td>\n",
       "        <td>2877</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>64</td>\n",
       "        <td>77</td>\n",
       "        <td>3627</td>\n",
       "        <td>77</td>\n",
       "        <td>64</td>\n",
       "        <td>3427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>50</td>\n",
       "        <td>3622</td>\n",
       "        <td>50</td>\n",
       "        <td>70</td>\n",
       "        <td>3033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>70</td>\n",
       "        <td>3526</td>\n",
       "        <td>70</td>\n",
       "        <td>55</td>\n",
       "        <td>3510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>55</td>\n",
       "        <td>3510</td>\n",
       "        <td>55</td>\n",
       "        <td>70</td>\n",
       "        <td>3526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>65</td>\n",
       "        <td>69</td>\n",
       "        <td>3495</td>\n",
       "        <td>69</td>\n",
       "        <td>65</td>\n",
       "        <td>6216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>70</td>\n",
       "        <td>3477</td>\n",
       "        <td>70</td>\n",
       "        <td>74</td>\n",
       "        <td>2866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>69</td>\n",
       "        <td>39</td>\n",
       "        <td>3438</td>\n",
       "        <td>39</td>\n",
       "        <td>69</td>\n",
       "        <td>2854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>77</td>\n",
       "        <td>64</td>\n",
       "        <td>3427</td>\n",
       "        <td>64</td>\n",
       "        <td>77</td>\n",
       "        <td>3627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>50</td>\n",
       "        <td>3231</td>\n",
       "        <td>50</td>\n",
       "        <td>60</td>\n",
       "        <td>6164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "        <td>70</td>\n",
       "        <td>3190</td>\n",
       "        <td>70</td>\n",
       "        <td>67</td>\n",
       "        <td>1579</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>60</td>\n",
       "        <td>3116</td>\n",
       "        <td>60</td>\n",
       "        <td>74</td>\n",
       "        <td>4269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>70</td>\n",
       "        <td>3033</td>\n",
       "        <td>70</td>\n",
       "        <td>50</td>\n",
       "        <td>3622</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(69, 65, 6216, 65, 69, 3495),\n",
       " (50, 60, 6164, 60, 50, 3231),\n",
       " (65, 70, 5041, 70, 65, 2320),\n",
       " (61, 50, 4839, 50, 61, 4357),\n",
       " (50, 61, 4357, 61, 50, 4839),\n",
       " (60, 74, 4269, 74, 60, 3116),\n",
       " (51, 70, 3967, 70, 51, 3031),\n",
       " (74, 61, 3903, 61, 74, 2877),\n",
       " (64, 77, 3627, 77, 64, 3427),\n",
       " (70, 50, 3622, 50, 70, 3033),\n",
       " (55, 70, 3526, 70, 55, 3510),\n",
       " (70, 55, 3510, 55, 70, 3526),\n",
       " (65, 69, 3495, 69, 65, 6216),\n",
       " (74, 70, 3477, 70, 74, 2866),\n",
       " (69, 39, 3438, 39, 69, 2854),\n",
       " (77, 64, 3427, 64, 77, 3627),\n",
       " (60, 50, 3231, 50, 60, 6164),\n",
       " (67, 70, 3190, 70, 67, 1579),\n",
       " (74, 60, 3116, 60, 74, 4269),\n",
       " (50, 70, 3033, 70, 50, 3622)]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select * from (select start_station_id, end_station_id, count(*) as trip_count\n",
    "from trip group by start_station_id, end_station_id \n",
    "order by trip_count desc) as p1,\n",
    "(select start_station_id, end_station_id, count(*) as trip_count\n",
    "from trip group by start_station_id, end_station_id \n",
    "order by trip_count desc) as p2 \n",
    "where \n",
    "p1.start_station_id = p2.end_station_id \n",
    "and p1.end_station_id = p2.start_station_id limit 20;"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "由上表可知，站点A到站点B的租车量与站点B到站点A的租车辆相似。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.4.4 (练习题) 查询自行车#697的累积行驶时间。查询结果模式为(end_time, cumulative_traveling_duration)，按照累积行驶时间升序排列。\n",
    "\n",
    "例如，如果自行车#697的租借历史如下表：\n",
    "<table border=\"1\">\n",
    "  <tr>\n",
    "    <th>End Time</th>\n",
    "    <th>Duration</th>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>2019/02/27 10:15</td>\n",
    "    <td>240</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>2019/02/28 12:15</td>\n",
    "    <td>360</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>2019/03/01 09:50</td>\n",
    "    <td>200</td>\n",
    "  </tr>\n",
    "</table>\n",
    "\n",
    "查询结果如下表：\n",
    "<table border=\"1\">\n",
    "  <tr>\n",
    "    <th>End Time</th>\n",
    "    <th>Cumulative Traveling Duration</th>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>2019/02/27 10:15</td>\n",
    "    <td>240</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>2019/02/28 12:15</td>\n",
    "    <td>600</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>2019/03/01 09:50</td>\n",
    "    <td>800</td>\n",
    "  </tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "24 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>End Time</th>\n",
       "        <th>Cumulative Traveling Duration</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-10-15 18:54:00</td>\n",
       "        <td>370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-10-17 07:19:00</td>\n",
       "        <td>689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-10-18 17:56:00</td>\n",
       "        <td>1084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-10-20 17:45:00</td>\n",
       "        <td>14747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-10-25 18:46:00</td>\n",
       "        <td>16686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-10-29 10:23:00</td>\n",
       "        <td>16946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-10-30 10:41:00</td>\n",
       "        <td>17218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-10-30 18:42:00</td>\n",
       "        <td>17513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-10-31 07:55:00</td>\n",
       "        <td>17752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-10-31 16:18:00</td>\n",
       "        <td>17956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-11-04 09:52:00</td>\n",
       "        <td>18227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-11-06 06:57:00</td>\n",
       "        <td>18406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-11-06 08:02:00</td>\n",
       "        <td>18634</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-11-07 09:07:00</td>\n",
       "        <td>18855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-11-07 14:04:00</td>\n",
       "        <td>19143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-11-07 16:58:00</td>\n",
       "        <td>19474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-11-08 17:33:00</td>\n",
       "        <td>19725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-11-08 18:01:00</td>\n",
       "        <td>20021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-11-20 23:50:00</td>\n",
       "        <td>20729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-11-29 04:26:00</td>\n",
       "        <td>21901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-12-13 08:01:00</td>\n",
       "        <td>22090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-12-17 07:26:00</td>\n",
       "        <td>22567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-12-20 20:28:00</td>\n",
       "        <td>23347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-12-20 20:41:00</td>\n",
       "        <td>23572</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2013, 10, 15, 18, 54), 370),\n",
       " (datetime.datetime(2013, 10, 17, 7, 19), 689),\n",
       " (datetime.datetime(2013, 10, 18, 17, 56), 1084),\n",
       " (datetime.datetime(2013, 10, 20, 17, 45), 14747),\n",
       " (datetime.datetime(2013, 10, 25, 18, 46), 16686),\n",
       " (datetime.datetime(2013, 10, 29, 10, 23), 16946),\n",
       " (datetime.datetime(2013, 10, 30, 10, 41), 17218),\n",
       " (datetime.datetime(2013, 10, 30, 18, 42), 17513),\n",
       " (datetime.datetime(2013, 10, 31, 7, 55), 17752),\n",
       " (datetime.datetime(2013, 10, 31, 16, 18), 17956),\n",
       " (datetime.datetime(2013, 11, 4, 9, 52), 18227),\n",
       " (datetime.datetime(2013, 11, 6, 6, 57), 18406),\n",
       " (datetime.datetime(2013, 11, 6, 8, 2), 18634),\n",
       " (datetime.datetime(2013, 11, 7, 9, 7), 18855),\n",
       " (datetime.datetime(2013, 11, 7, 14, 4), 19143),\n",
       " (datetime.datetime(2013, 11, 7, 16, 58), 19474),\n",
       " (datetime.datetime(2013, 11, 8, 17, 33), 19725),\n",
       " (datetime.datetime(2013, 11, 8, 18, 1), 20021),\n",
       " (datetime.datetime(2013, 11, 20, 23, 50), 20729),\n",
       " (datetime.datetime(2013, 11, 29, 4, 26), 21901),\n",
       " (datetime.datetime(2013, 12, 13, 8, 1), 22090),\n",
       " (datetime.datetime(2013, 12, 17, 7, 26), 22567),\n",
       " (datetime.datetime(2013, 12, 20, 20, 28), 23347),\n",
       " (datetime.datetime(2013, 12, 20, 20, 41), 23572)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select end_time as \"End Time\", (select sum(duration) from trip b where b.end_time <= a.end_time and bike_id = 697) \"Cumulative Traveling Duration\" from trip a where bike_id = 697 group by id order by \"Cumulative Traveling Duration\" asc;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.4.5 查询每个城市最受欢迎的站点 (5分)\n",
    "\n",
    "最受欢迎的站点是指用户使用次数最多的站点，一次租车记录，用户既使用了一次租车站点，又使用了一次还车站点。对于self-loop站点，用户使用了该站点两次。查询结果模式为(city, station_name, visit_count)，按城市名称字母序排列。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>city</th>\n",
       "        <th>station_name</th>\n",
       "        <th>visit_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mountain View</td>\n",
       "        <td>Mountain View Caltrain Station</td>\n",
       "        <td>13263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Palo Alto</td>\n",
       "        <td>Palo Alto Caltrain Station</td>\n",
       "        <td>3930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Redwood City</td>\n",
       "        <td>Redwood City Caltrain Station</td>\n",
       "        <td>2809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>San Francisco Caltrain (Townsend at 4th)</td>\n",
       "        <td>112271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Jose</td>\n",
       "        <td>San Jose Diridon Caltrain Station</td>\n",
       "        <td>18973</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Mountain View', 'Mountain View Caltrain Station', 13263),\n",
       " ('Palo Alto', 'Palo Alto Caltrain Station', 3930),\n",
       " ('Redwood City', 'Redwood City Caltrain Station', 2809),\n",
       " ('San Francisco', 'San Francisco Caltrain (Townsend at 4th)', 112271),\n",
       " ('San Jose', 'San Jose Diridon Caltrain Station', 18973)]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select station.city,station_name,max as visit_count from (\n",
    "select city,max(count)\n",
    "from\n",
    "    (select s1.id as id, s1.count+s2.count as count from \n",
    "        (select start_station_id as id, count(*)\n",
    "             from trip group by start_station_id) as s1,\n",
    "        (select end_station_id as id, count(*)\n",
    "            from trip group by end_station_id) as s2 \n",
    "    where s1.id = s2.id) as counts, station\n",
    "where counts.id =station_id \n",
    "group by city\n",
    ") as maxCity,station,(select s1.id as id, s1.count+s2.count as count from \n",
    "        (select start_station_id as id, count(*)\n",
    "             from trip group by start_station_id) as s1,\n",
    "        (select end_station_id as id, count(*)\n",
    "            from trip group by end_station_id) as s2 \n",
    "    where s1.id = s2.id) as counts\n",
    "where counts.id = station_id and counts.count = max and maxCity.city = station.city\n",
    "order by station.city;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "基于查询结果，分析这些站点使用较多的原因？"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "原因是它们是火车站。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.4.6 查询每个站点当前的自行车数目 (5分)\n",
    "\n",
    "假设所有自行车至少被租借过一次，查询每个站点当前的自行车数目，即每个站点可借车的数目，当前可以理解为数据库中最后一次还车记录完成时。查询结果模式为(station_id, bike_count)，按bike_count降序排列，如果bike_count相同，按station_id升序排列。通常，公共自行车服务需要保证每个站点都有车可借，也有车可还。如果某个站点自行车满了，而其他站点自行车借完了，需要进行自行车服务调度。\n",
    "\n",
    "时空查询：trip是一个**时空**关系，即保留了历史的租车记录，当查询**当前**情况时，需要使用每辆自行车时间最近的那条租车记录。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "70 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>station_id</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>25</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>26</td>\n",
       "        <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>27</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>28</td>\n",
       "        <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>29</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>30</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>31</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>33</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>34</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>35</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>36</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>37</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39</td>\n",
       "        <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>41</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>42</td>\n",
       "        <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>46</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>47</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>49</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>51</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>54</td>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>56</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>57</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>58</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>59</td>\n",
       "        <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>61</td>\n",
       "        <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>62</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>63</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>64</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>65</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>66</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>68</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>69</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>73</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>75</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>76</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>77</td>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>80</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>82</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>83</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>84</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 19),\n",
       " (3, 9),\n",
       " (4, 7),\n",
       " (5, 10),\n",
       " (6, 21),\n",
       " (7, 14),\n",
       " (8, 17),\n",
       " (9, 8),\n",
       " (10, 3),\n",
       " (11, 4),\n",
       " (12, 13),\n",
       " (13, 5),\n",
       " (14, 1),\n",
       " (16, 7),\n",
       " (21, 8),\n",
       " (22, 7),\n",
       " (23, 14),\n",
       " (24, 4),\n",
       " (25, 10),\n",
       " (26, 16),\n",
       " (27, 8),\n",
       " (28, 14),\n",
       " (29, 13),\n",
       " (30, 5),\n",
       " (31, 10),\n",
       " (32, 4),\n",
       " (33, 5),\n",
       " (34, 6),\n",
       " (35, 7),\n",
       " (36, 11),\n",
       " (37, 5),\n",
       " (38, 9),\n",
       " (39, 14),\n",
       " (41, 3),\n",
       " (42, 9),\n",
       " (45, 2),\n",
       " (46, 8),\n",
       " (47, 13),\n",
       " (48, 4),\n",
       " (49, 2),\n",
       " (50, 26),\n",
       " (51, 1),\n",
       " (54, 18),\n",
       " (55, 24),\n",
       " (56, 5),\n",
       " (57, 4),\n",
       " (58, 6),\n",
       " (59, 9),\n",
       " (60, 9),\n",
       " (61, 23),\n",
       " (62, 2),\n",
       " (63, 4),\n",
       " (64, 4),\n",
       " (65, 13),\n",
       " (66, 5),\n",
       " (67, 5),\n",
       " (68, 8),\n",
       " (69, 50),\n",
       " (70, 45),\n",
       " (71, 6),\n",
       " (72, 13),\n",
       " (73, 7),\n",
       " (74, 14),\n",
       " (75, 7),\n",
       " (76, 8),\n",
       " (77, 17),\n",
       " (80, 5),\n",
       " (82, 7),\n",
       " (83, 3),\n",
       " (84, 3)]"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select end_station_id as station_id,count(*) from trip,\n",
    "(select bike_id, max(end_time) from trip group by bike_id) as last\n",
    "where last.max = trip.end_time and last.bike_id = trip.bike_id \n",
    "group by end_station_id;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "基于每个站点的车位数，分析查询结果是否存在问题？如何解释这一结果？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>station_id</th>\n",
       "        <th>station_name</th>\n",
       "        <th>lat</th>\n",
       "        <th>long</th>\n",
       "        <th>dock_count</th>\n",
       "        <th>city</th>\n",
       "        <th>installation_date</th>\n",
       "        <th>zip_code</th>\n",
       "        <th>station_id_1</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>San Pedro Square</td>\n",
       "        <td>37.33672</td>\n",
       "        <td>-121.89407</td>\n",
       "        <td>15</td>\n",
       "        <td>San Jose</td>\n",
       "        <td>2013-08-07</td>\n",
       "        <td>95113</td>\n",
       "        <td>6</td>\n",
       "        <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>San Salvador at 1st</td>\n",
       "        <td>37.330166</td>\n",
       "        <td>-121.88583</td>\n",
       "        <td>15</td>\n",
       "        <td>San Jose</td>\n",
       "        <td>2013-08-05</td>\n",
       "        <td>95113</td>\n",
       "        <td>8</td>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>26</td>\n",
       "        <td>Redwood City Medical Center</td>\n",
       "        <td>37.487682</td>\n",
       "        <td>-122.223495</td>\n",
       "        <td>15</td>\n",
       "        <td>Redwood City</td>\n",
       "        <td>2013-08-12</td>\n",
       "        <td>94063</td>\n",
       "        <td>26</td>\n",
       "        <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>Harry Bridges Plaza (Ferry Building)</td>\n",
       "        <td>37.79539</td>\n",
       "        <td>-122.3942</td>\n",
       "        <td>23</td>\n",
       "        <td>San Francisco</td>\n",
       "        <td>2013-08-20</td>\n",
       "        <td>94107</td>\n",
       "        <td>50</td>\n",
       "        <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>54</td>\n",
       "        <td>Embarcadero at Bryant</td>\n",
       "        <td>37.78715</td>\n",
       "        <td>-122.388016</td>\n",
       "        <td>15</td>\n",
       "        <td>San Francisco</td>\n",
       "        <td>2013-08-20</td>\n",
       "        <td>94107</td>\n",
       "        <td>54</td>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>Temporary Transbay Terminal (Howard at Beale)</td>\n",
       "        <td>37.789757</td>\n",
       "        <td>-122.394646</td>\n",
       "        <td>23</td>\n",
       "        <td>San Francisco</td>\n",
       "        <td>2013-08-20</td>\n",
       "        <td>94107</td>\n",
       "        <td>55</td>\n",
       "        <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>69</td>\n",
       "        <td>San Francisco Caltrain 2 (330 Townsend)</td>\n",
       "        <td>37.7766</td>\n",
       "        <td>-122.39547</td>\n",
       "        <td>23</td>\n",
       "        <td>San Francisco</td>\n",
       "        <td>2013-08-23</td>\n",
       "        <td>94107</td>\n",
       "        <td>69</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>San Francisco Caltrain (Townsend at 4th)</td>\n",
       "        <td>37.776615</td>\n",
       "        <td>-122.39526</td>\n",
       "        <td>19</td>\n",
       "        <td>San Francisco</td>\n",
       "        <td>2013-08-23</td>\n",
       "        <td>94107</td>\n",
       "        <td>70</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(6, 'San Pedro Square', 37.33672, -121.89407, 15, 'San Jose', datetime.date(2013, 8, 7), '95113', 6, 21),\n",
       " (8, 'San Salvador at 1st', 37.330166, -121.88583, 15, 'San Jose', datetime.date(2013, 8, 5), '95113', 8, 17),\n",
       " (26, 'Redwood City Medical Center', 37.487682, -122.223495, 15, 'Redwood City', datetime.date(2013, 8, 12), '94063', 26, 16),\n",
       " (50, 'Harry Bridges Plaza (Ferry Building)', 37.79539, -122.3942, 23, 'San Francisco', datetime.date(2013, 8, 20), '94107', 50, 26),\n",
       " (54, 'Embarcadero at Bryant', 37.78715, -122.388016, 15, 'San Francisco', datetime.date(2013, 8, 20), '94107', 54, 18),\n",
       " (55, 'Temporary Transbay Terminal (Howard at Beale)', 37.789757, -122.394646, 23, 'San Francisco', datetime.date(2013, 8, 20), '94107', 55, 24),\n",
       " (69, 'San Francisco Caltrain 2 (330 Townsend)', 37.7766, -122.39547, 23, 'San Francisco', datetime.date(2013, 8, 23), '94107', 69, 50),\n",
       " (70, 'San Francisco Caltrain (Townsend at 4th)', 37.776615, -122.39526, 19, 'San Francisco', datetime.date(2013, 8, 23), '94107', 70, 45)]"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select * from station,\n",
    "(select end_station_id as station_id,count(*) from trip,\n",
    "        (select bike_id, max(end_time) from trip group by bike_id) as last\n",
    "    where last.max = trip.end_time and last.bike_id = trip.bike_id \n",
    "group by end_station_id) as lastCount\n",
    "where lastCount.station_id = station.station_id and dock_count < count;"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "存在不少问题；\n",
    "没有站点无车可借，但部分热门站点的自行车数量超过了可停靠的数量。最好需要给比如火车站这样的地方多建造一点泊车位。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.4.7 分析气温与租车之间的关联关系 (4分)\n",
    "\n",
    "查询2014年zip_code为94107区域内不同月份对应的气温及其日平均租车数量。查询模式为(month, temp, number)，month为月份，temp为该月对应的平均气温，number为该月日平均租车数量，仅考虑租车（非还车）时的时间和所在自行车站点的zipcode。在PostgreSQL中，可使用[extract](https://www.postgresql.org/docs/current/functions-datetime.html)函数从timestamp类型变量中提取年月日信息，如：extract(year from date)，即可得到date变量中的年份信息。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "12 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>month</th>\n",
       "        <th>temp</th>\n",
       "        <th>number</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3.0</td>\n",
       "        <td>59.71935483870968</td>\n",
       "        <td>709.0645161290322581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7.0</td>\n",
       "        <td>69.23870967741935</td>\n",
       "        <td>895.3548387096774194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1.0</td>\n",
       "        <td>52.80967741935484</td>\n",
       "        <td>709.6451612903225806</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2.0</td>\n",
       "        <td>56.135714285714286</td>\n",
       "        <td>610.1428571428571429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6.0</td>\n",
       "        <td>66.34666666666666</td>\n",
       "        <td>883.2000000000000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8.0</td>\n",
       "        <td>69.56172839506173</td>\n",
       "        <td>891.4461538461538462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12.0</td>\n",
       "        <td>51.62903225806452</td>\n",
       "        <td>586.3870967741935484</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10.0</td>\n",
       "        <td>63.229032258064514</td>\n",
       "        <td>1002.9032258064516129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9.0</td>\n",
       "        <td>67.73</td>\n",
       "        <td>951.1666666666666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4.0</td>\n",
       "        <td>59.913333333333334</td>\n",
       "        <td>780.6000000000000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5.0</td>\n",
       "        <td>62.244299674267104</td>\n",
       "        <td>810.7741935483870968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11.0</td>\n",
       "        <td>56.94</td>\n",
       "        <td>770.5666666666666667</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(3.0, 59.71935483870968, Decimal('709.0645161290322581')),\n",
       " (7.0, 69.23870967741935, Decimal('895.3548387096774194')),\n",
       " (1.0, 52.80967741935484, Decimal('709.6451612903225806')),\n",
       " (2.0, 56.135714285714286, Decimal('610.1428571428571429')),\n",
       " (6.0, 66.34666666666666, Decimal('883.2000000000000000')),\n",
       " (8.0, 69.56172839506173, Decimal('891.4461538461538462')),\n",
       " (12.0, 51.62903225806452, Decimal('586.3870967741935484')),\n",
       " (10.0, 63.229032258064514, Decimal('1002.9032258064516129')),\n",
       " (9.0, 67.73, Decimal('951.1666666666666667')),\n",
       " (4.0, 59.913333333333334, Decimal('780.6000000000000000')),\n",
       " (5.0, 62.244299674267104, Decimal('810.7741935483870968')),\n",
       " (11.0, 56.94, Decimal('770.5666666666666667'))]"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select extract(month from date) as month ,avg(mean_temp) as temp,avg(count) as number from (\n",
    "    select extract(doy from start_time),count(*) from trip \n",
    "    where start_station_id in (select station_id from station where zip_code = '94107') \n",
    "        and extract(year from start_time) = 2014\n",
    "    group by extract(doy from start_time)\n",
    ") as days, weather where extract(doy from date) = date_part \n",
    "group by  extract(month from date);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "从查询结果中，你发现了什么规律？为了更有效地分析查询结果，可以通过直方图可视化查询结果，进行分析。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "12 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<BarContainer object of 12 artists>"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYIAAAEXCAYAAACgUUN5AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvIxREBQAAGehJREFUeJzt3Xm4ZHV95/H3h24QRXYuiyLgguDKYou4TFwQEUXABR9RtKPENm7R4IbJxDUzkhgX5ploJKL2RBYJLrhElEEdRx3RZgmiYEBEJSzd4oYaQeQ7f/zOlbLT3ff2vXXqdvd5v57nPlXn1PL9naq69Tnn9zunTqoKSdJwbbbQDZAkLSyDQJIGziCQpIEzCCRp4AwCSRo4g0CSBs4gkKSB6y0IkuyT5JKRv18keWWSHZKcl+TK7nL7vtogSZpZJnFAWZJFwL8DDwNeCvykqk5KciKwfVW9rvdGSJLWaFJdQ4cA36uqHwBHAcu7+cuBoyfUBknSGiyeUJ1nAWd013epqusBqur6JDvP9OCddtqp9tprrx6bJ0mbngsvvPDHVTU10/16D4IkWwBHAq9fz8ctA5YB7LHHHqxYsaKH1knSpivJD2Zzv0l0DR0OXFRVN3bTNybZDaC7XLmmB1XVKVW1pKqWTE3NGGiSpDmaRBAcyx3dQgCfBJZ215cC50ygDZKkteg1CJLcBTgU+NjI7JOAQ5Nc2d12Up9tkCStW69jBFX1a2DH1ebdRNuLSJK0AfDIYkkaOINAkgbOIJCkgTMIJGngJnVksaSNyF4nfmbsz3nNSU8e+3NqPAwCaSMz7i9pv6BlEEhj4he0NlaOEUjSwBkEkjRwBoEkDZxBIEkDZxBI0sAZBJI0cO4+KmnBeODahsEtAkkaOINAkgbOriEtmEl1C9j9IK2bWwSSNHAGgSQNnEEgSQNnEEjSwBkEkjRwvQZBku2SnJ3kiiSXJ3l4kh2SnJfkyu5y+z7bIElat763CE4Gzq2qfYH9gMuBE4Hzq2pv4PxuWpK0QHoLgiTbAH8EnApQVbdW1c+Ao4Dl3d2WA0f31QZJ0sz63CK4F7AK+GCSi5O8P8lWwC5VdT1Ad7lzj22QJM2gzyBYDBwIvLeqDgB+xXp0AyVZlmRFkhWrVq3qq42SNHh9BsG1wLVVdUE3fTYtGG5MshtAd7lyTQ+uqlOqaklVLZmamuqxmZI0bL391lBV3ZDkR0n2qarvAocA3+n+lgIndZfn9NUGzY2/zSMNS98/Ovdy4LQkWwBXA8+nbYWcleR44IfAMT23YZMy7i9pv6Al9RoEVXUJsGQNNx3SZ11J0ux5ZLEkDZxBIEkDZxBI0sAZBJI0cAaBJA2c5yyWtMnz2Jh1c4tAkgbOIJCkgTMIJGngNvkxAvsGJWndNvkgmBR/A0jSxvo9YNeQJA2cQSBJA2cQSNLAGQSSNHAGgSQNnEEgSQNnEEjSwBkEkjRwBoEkDZxBIEkDZxBI0sD1+ltDSa4BbgZ+B9xWVUuS7AB8BNgLuAZ4ZlX9tM92SJLWbhJbBI+tqv2rakk3fSJwflXtDZzfTUuSFshCdA0dBSzvri8Hjl6ANkiSOn0HQQGfT3JhkmXdvF2q6nqA7nLnntsgSVqHvs9H8Miqui7JzsB5Sa6Y7QO74FgGsMcee/TVPkkavF63CKrquu5yJfBx4CDgxiS7AXSXK9fy2FOqaklVLZmamuqzmZI0aL0FQZKtkmw9fR14AnAZ8ElgaXe3pcA5fbVBkjSzPruGdgE+nmS6zulVdW6SbwJnJTke+CFwTI9tkCTNoLcgqKqrgf3WMP8m4JC+6kqS1o9HFkvSwBkEkjRwBoEkDZxBIEkDZxBI0sAZBJI0cAaBJA2cQSBJA2cQSNLAGQSSNHAGgSQNnEEgSQNnEEjSwBkEkjRwBoEkDZxBIEkDZxBI0sAZBJI0cAaBJA2cQSBJA2cQSNLAGQSSNHC9B0GSRUkuTvLpbvqeSS5IcmWSjyTZou82SJLWbhJbBK8ALh+Z/hvgXVW1N/BT4PgJtEGStBa9BkGS3YEnA+/vpgM8Dji7u8ty4Og+2yBJWre+twjeDbwWuL2b3hH4WVXd1k1fC9y95zZIktahtyBIcgSwsqouHJ29hrvWWh6/LMmKJCtWrVrVSxslSf1uETwSODLJNcCZtC6hdwPbJVnc3Wd34Lo1PbiqTqmqJVW1ZGpqqsdmStKw9RYEVfX6qtq9qvYCngV8oaqeA3wReEZ3t6XAOX21QZI0s1kFQZJdkxyZ5ClJdp1nzdcBJyS5ijZmcOo8n0+SNA8zBkGSPwG+ATyNtib/9SQvWJ8iVfWlqjqiu351VR1UVfepqmOq6pa5NFySNB6LZ74LrwEOqKqbAJLsCHwN+ECfDZMkTcZsuoauBW4emb4Z+FE/zZEkTdpstgj+HbggyTm0XT2PAr6R5ASAqnpnj+2TJPVsNkHwve5v2vRePluPvzmSpEmbMQiq6s2TaIgkaWHMGARJlgB/Cew5ev+qenCP7ZIkTchsuoZOo+059C3u+M0gSdImYjZBsKqqPtl7SyRJC2I2QfDGJO8Hzgd+f/BXVX2st1ZJkiZmNkHwfGBfYHPu6BoqwCCQpE3AbIJgv6p6UO8tkSQtiNkcWfz1JPfvvSWSpAUxmy2CRwFLk3yfNkYQoNx9VJI2DbMJgif23gpJ0oKZsWuoqn4A3AN4XHf917N5nCRp4zCb8xG8kXYymdd3szYHPtxnoyRJkzObNfunAkcCvwKoquvwB+ckaZMxmyC4taqKduwASbbqt0mSpEmaTRCcleR9wHZJXgj8b+Af+22WJGlSZrPX0BRwNvALYB/gDcDj+2yUJGlyZhMEh1bV64DzpmckeQdtAFmStJFbaxAkeTHwEuBeSS4duWlr4Kt9N0ySNBnr2iI4Hfgs8DbgxJH5N1fVT2Z64iRbAl8G7tTVObuq3pjknsCZwA7ARcBzq+rWObZfkjRPax0srqqfV9U1VXVsVf1g5G/GEOjcQjsIbT9gf+CJSQ4G/gZ4V1XtDfwUOH6+CyFJmrvejhCu5pfd5ObdXwGPow0+AywHju6rDZKkmfX6UxFJFiW5BFhJG2z+HvCzqrqtu8u1wN37bIMkad16DYKq+l1V7Q/sDhwE3G9Nd1vTY5MsS7IiyYpVq1b12UxJGrSJ/HhcVf0M+BJwMO3AtOlB6t2B69bymFOqaklVLZmamppEMyVpkHoLgiRTSbbrrt+ZdhDa5cAXgWd0d1sKnNNXGyRJM5vNAWVztRuwPMkiWuCcVVWfTvId4Mwkfw1cDJzaYxskSTPoLQiq6lLggDXMv5o2XiBJ2gB4ghlJGjiDQJIGziCQpIEzCCRp4AwCSRo4g0CSBs4gkKSBMwgkaeAMAkkaOINAkgbOIJCkgTMIJGngDAJJGjiDQJIGziCQpIEzCCRp4AwCSRo4g0CSBs4gkKSBMwgkaeAMAkkauN6CIMk9knwxyeVJvp3kFd38HZKcl+TK7nL7vtogSZpZn1sEtwGvqqr7AQcDL01yf+BE4Pyq2hs4v5uWJC2Q3oKgqq6vqou66zcDlwN3B44Clnd3Ww4c3VcbJEkzm8gYQZK9gAOAC4Bdqup6aGEB7DyJNkiS1qz3IEhyV+CjwCur6hfr8bhlSVYkWbFq1ar+GihJA9drECTZnBYCp1XVx7rZNybZrbt9N2Dlmh5bVadU1ZKqWjI1NdVnMyVp0PrcayjAqcDlVfXOkZs+CSztri8FzumrDZKkmS3u8bkfCTwX+FaSS7p5fwGcBJyV5Hjgh8AxPbZBkjSD3oKgqr4CZC03H9JXXUnS+vHIYkkaOINAkgbOIJCkgTMIJGngDAJJGjiDQJIGziCQpIEzCCRp4AwCSRo4g0CSBs4gkKSBMwgkaeAMAkkaOINAkgbOIJCkgTMIJGngDAJJGjiDQJIGziCQpIEzCCRp4AwCSRo4g0CSBq63IEjygSQrk1w2Mm+HJOclubK73L6v+pKk2elzi+BDwBNXm3cicH5V7Q2c301LkhZQb0FQVV8GfrLa7KOA5d315cDRfdWXJM3OpMcIdqmq6wG6y50nXF+StJoNdrA4ybIkK5KsWLVq1UI3R5I2WZMOghuT7AbQXa5c2x2r6pSqWlJVS6ampibWQEkamkkHwSeBpd31pcA5E64vSVpNn7uPngH8P2CfJNcmOR44CTg0yZXAod20JGkBLe7riavq2LXcdEhfNSVJ62+DHSyWJE2GQSBJA2cQSNLAGQSSNHAGgSQNnEEgSQNnEEjSwBkEkjRwBoEkDZxBIEkDZxBI0sAZBJI0cAaBJA2cQSBJA2cQSNLAGQSSNHAGgSQNnEEgSQNnEEjSwBkEkjRwBoEkDZxBIEkDtyBBkOSJSb6b5KokJy5EGyRJzcSDIMki4O+Bw4H7A8cmuf+k2yFJahZii+Ag4KqqurqqbgXOBI5agHZIkliYILg78KOR6Wu7eZKkBZCqmmzB5BjgsKr6k276ucBBVfXy1e63DFjWTe4DfLfnpu0E/LjnGtbZsOtsSstinQ23xiTr7FlVUzPdafEEGrK6a4F7jEzvDly3+p2q6hTglEk1KsmKqlpineHW2ZSWxTobbo1J1pmthega+iawd5J7JtkCeBbwyQVohySJBdgiqKrbkrwM+BywCPhAVX170u2QJDUL0TVEVf0L8C8LUXsdJtUNZZ0Nt86mtCzW2XBrTLLOrEx8sFiStGHxJyYkaeAMAkkaOINAkgZuQQaLN3RJNquq23t43oOAzYHbquqCcT//SJ39gVsAquryTaDOpF633utsgp+BiSzPSL0pYPOqum5kXsrBznlxiwBI8uQkb07ytiQ79hQCh9GOl3gycEaSlyW5aw91Dgc+BbwE+Ockzx93jQnXmdTr1nudTfAzMJHlGan3DOAzwDlJ3prkvwBUVSXJmGvtmeS+q83b6GrMWlUN+g94GPB94NnAPwBfBR5BW+sYx/MHuBPwIeCZ3bz9gfOAVwN3HmOdu9J2yz2ym3cwcBXwp2N8vSZZZ1KvW691NtHPQO/Ls1rNHYGvAPsBuwJ/Dfwd8LQeaj0DuBj4OvBO4LjRZd9YaqzPn1sE8EDg81V1elX9KfBR4LXAgdC6iebz5NXcAlwOPDjJXavqEuCVwJOAF8yr9X9Y55fACmCbJJtX1ddpR26/LsnSjbDOpF63Xutsop+B3pdnNYto4fObqroBeBfwQ+DhSQ4eV5EkWwEvBl4IPA74NnBwkldCW/aNocb6MgjaT17cOcm+AFX1Ttqax7uTbFfj6ya6lLZWc+8ki6sdTf0a4IQk+42pBsANwCHAnQGqagXwXODlSe65Edbp9XUb2RTvrc7IysSm9hmY1PJQVStpK2nHJ7lbVd0EnN7d/KQxlgptzGNRVf0aOIv2Kwj3SvLMjajGejEI2j/NbcChSXYCqKq/Ay4DXjSuIlX1WeCXwCuAB3ZrURcC59I+GOOq8x7gLsA/JNm2Wyv8Cu2fdhxrM4smVCddnV5et26t7PdrX33USbJ7ki2mVyY2oc9Ar+/NOnyxu3xWFwY/Bk4GHjv9vztf3RbVmcBrkty7qm4G/g9wBa0beaOosb4GeWRxkkVV9buR6QNofY7nAl+qqm+lnULz9qr62zk8/32A7YDLquo3q932t8DWwG9o52V4FfDIqrpmDnUeQPs528urauXo3hNJzgT+g9YHuRg4AXh0VV07hzqPAu5ZVf/UTW9R7aRC467zFOBeVXVyN/37vbfG/LodBRwKvKV73cZepxtIfRPwnKq6uvsy/m0Py3IwsDdwJXDR9PvS3TbO92ZvYFtavzar/f+MbXnWUvv3/69Jngw8mtZNdArtLIevAJ7YfcHOp06qqpLsRuu22Qk4uaq+l2Rr2vfDs6vqBxtyjTmpCQ9KLOQfcN+R64umvzO7ywNog8VnAmcA3wMeNIcaR9DWvL7YPc8Du/mbj9znsbQP798D95/jshze1fkEbU+Ku6+hzguA/wr8M/CAOdTYjDb4+G3gO4wMOAJbjqtO9xxPAC4BDl1t/qIxv26Ppq15HbqO+8yrzsiyXEP7J+9rWY7sPgPLgbOBvafftzG/N0cD/0rrmjmZtjfSVuN8zVZ7rod179NDR+aNfq4PpA1Kfxk4HzhwPvXW0ob9gDfQ9op6JHAs8A1gh3k+72Z915hTuyZdcKH+ui/oXwOnj8ybDoPNusudaGtXz6atAa9vjUd0XzIHdNPvof266n/6EHTTi+e4LI8B/o12Qh+AjwOPH12m1e5/p3m+dq+lreX9L+DP13G/OdXpXrcbR5ZnW2BPYKs1vUZzfd26x54AvLq7fjfalsHDgO3GUQd4PG0vnQfQ+oE/D/xRD5+BHWn9ytMrGh8AjgF2Bu4yxvdmR+Cz01/utGD5Ji1cthnne9M9/nDa1s0ptJWcU9e2DN3/61bzqPWf2r/a7VO07uFP01a21jtwaKH1qOnP9uqfgXHUGMffIMYIuv7gl9H2arg1yYehbd52g1zTA8K3VdWV1fYg+v4cy51UVRd3198I7JDkTl2925M8NMkR3e2/W+MzzOxG4EVV9Y0ku9K+yF6W5H20QUGSPCTJgd39b13L88zWbbSTCS0HDkryziRv6+o8Ygx1bgJ+C+yWZEfaF8B7gQ9yx/I8tOsWgLm/btCWZdrZtC+2lwH/M8nmSfafZ51FwPOqDZxuRTuz3gPg990Ctyc5aAzLchttMHjfJNvQVg6eR9ub5i+6egeM4b25jbZVuCtAVX0A+AHtC+zJXZ2Hj+O96cafltK67JbRlmefJGd3tW/p7veoJHeqqh9X1a/mWOtpwP9N8rCstmfg9HRVraqq99H2unpqVV20njWOAE6lnWnx1Ule1D3v7SPjLPOqMS6DCILuw/IC2l4Grwa2HAmD2wC6vRyOS7LlyJ4k6+sC4GPd803v7rYnsE03b3dgX9rufVS3SjCH5bm8qqYHzo4H3lNVR9P6gp+UZC/gj+jO/DbXOiPOAW6oqvNpbX8xbQwE4KHzrVNV36V9qbyL1gVxOm0L7lzgsCR3B+4JXDSfOp0vAC/s+s//saqOpQX2r4DDgPvOp05Vfa6qvtaNO/yMtpb3xiQPqqpKsjlw7/kuS1X9HPgfwOtpWx0frKqn0L549kgy3b0y3/fm58BpwPOTPDfJf6ONBXyH1gUG7TM+7/em2jjAxSPTv6iqRwG7dCs50yt1j6FtDcxJ9/9xArAS+HPgwNH/+bpjvOiwJFtW1S9rZOxlljUOAP478MdV9Txa19y+IzWmx/LmXGOsFmIzZKH/aJu7HwU+3E0/GHg6sPMYayymrUmd300fB7wD2LrnZfssI2MhY3rOu9HWzl9I22x/A21T9tmM8eAX2sDfS1ebdy6wz5iX5ym0gwjfMjLv/cDTe3pP3kL7wp7ughzna7Y98HbgiJF5HwMeO8Ya2wLP6T4D7xqZ/5nucz6v5eEPx+6Oo+2xt8fIvJ1oW2/3o+2NtMU86+1BGzSHO/rol9B1a3HHuOGbgb3mWOMR/OGY2n1o/f/3GH29aDsUzKnGWD+jC92ABVvw9uH6IG3T/Upgt57qfAh4G3Ah8OAxP3dWm356V2fsy9J9mf0QeEo3/VjgHj2/R9PLs+uYn3cxrdvhatoW1fG0LZ379LgcX2Ge/efreP7Du8/yE2gDyBf18eXCH/ZtPw/4GnDXeT7n9NjdmSPz3krb+2g0DM4EDp5nrdHA2Xbk+l/RfpLjod30eu8kspYaU93lItruvJ+iG5egG9jfUP4WvAELuvBts/CG+bzx63juAFvQ9j76YZ9vPK0L6nja3j0P7KnGPYCHjExv1kedkdfuBbTuhznt6TLLOgfSNt/f0cdnYLVaZ/W15kfrpvsz2r7onwP263lZpt+beb1mtDGUc2l96B8Czhi57a20bsIXAX9JO4p5vXfgGHm+6cAZrbHFyPW/onVJngR8izn0Dqwl1Ka3Ajej/fTHNrRxr08B2/f5Pq3P3yCPIwBIsj3tn/NVVXVpj3X+GPhm9Xhe5q7f+VDge9X623szeqxCnzVo/ds3VNUVfdbq2yRer5FaW9O2En/Rc509abtzXjWG57ob8AtgS9ru27+tNm5DkqfSBqkfAry7qi6bY42taF3BH6N12SyuquO62+5UdwxCf4k2RnRYVX1rjDUW0VZuzgB+TvtdpudV1Xfmsjx9GGwQAHSDNL+Z+Z7zquFP5Eqz0O0xdgpwa1Ud2x0w+csaw8FVawic30x/UXe33xf4CG1w9197qvEJWtA8te8VtvU16CCQtGFJ+6mIt9PWqhcBj6k5HAk9Q43pwPmPqjou7dwN2wDfqfazFX3U2Bt4Pm0HlQ1mS2DaIHYflbRx6L6IL6XtqfTUcYdAV+Mm2tjDb5NcQdu186pxhcAaavwbbQ+rkzfEEACDQNIGpBu7exLwhPXtp18fI4GzHe2cBtfN8JD51NiGtmvyjeOuMS6eqlLSBqOqfprkKRMYu+s9cCYVauPgGIGkQZrQziK91xgHg0CSBs4xAkkaOINAkgbOIJCkgTMIpB4k2S7JS0amH5Pk0wvZJmltDAKpH9vRTukobfAMAg1ekr2SXJHk/UkuS3Jakscn+WqSK7sziu2Q5BNJLk3y9SQP7h77piQfSPKlJFcn+bPuaU8C7p3kkiRv7+bdNcnZXa3T5nECJGmsPKBMau5DO+fvMto5eZ9NO9fskbRTP/4IuLiqjk7yONr5m/fvHrsv7fwMWwPfTfJe4ETaT4LvD61rCDiAdtrK64Cv0k5Y/pVJLJy0Lm4RSM33q+pb1U5T+G3ameWK9tv0e9FC4Z8AquoLwI5Jtu0e+5mquqX7SYGVwC5rqfGNqrq2q3FJ97zSgjMIpOaWkeu3j0zfTnc6xjU8ZvpozNHH/o61b2nP9n7SRBkE0ux8mXbe3ulunh/PcAKYm2ldRdIGzzUSaXbeBHwwyaW00xEuXdedq+qmbrD5MuCztJ8hljZI/taQJA2cXUOSNHAGgSQNnEEgSQNnEEjSwBkEkjRwBoEkDZxBIEkDZxBI0sD9fyueTBkZrnbVAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "## 绘制气温-时间变化直方图（在sql中仅需输出month与temp）\n",
    "query = \"\"\"\n",
    "select extract(month from date) as month,avg(mean_temp) as temp  from weather where zip_code = '94107' and extract(year from date) = '2014' group by extract(month from date);\n",
    "\"\"\"\n",
    "result = %sql $query\n",
    "%matplotlib inline\n",
    "result.bar()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "12 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<BarContainer object of 12 artists>"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAY4AAAEXCAYAAAC6baP3AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvIxREBQAAGudJREFUeJzt3Xm4HHWZ6PHvS8ImW1giYgIEJcoossSwKFxFETAggqPMFUQi5hpnFLfoKDPeGRyce8VxQXxmZMwVMDoIMsgVFNThQbiO+oCGRbboEFEhwxYUAWVYIu/94/dr0xxOkq5zejmd8/08Tz+nq7q63l911+m3fktVRWYiSVKnNhh0ASRJw8XEIUlqxMQhSWrExCFJasTEIUlqxMQhSWrExCFJasTEIUlqxMQhSWrExCFJamTqoAvQC9ttt13OmjVr0MWQpKFy7bXX3p+Z09e13HqZOGbNmsXSpUsHXQxJGioR8atOlrOpSpLUiIlDktSIiUOS1IiJQ5LUiIlDktRIzxJHRJwdEfdFxM1t87aJiMsj4rb6d+s6PyLisxGxPCJujIg5be+ZX5e/LSLm96q8kqTO9LLG8UXg1SPmnQxckZmzgSvqNMA8YHZ9LATOhJJogFOA/YB9gVNayUaSNBg9SxyZ+T3gNyNmHwUsqc+XAEe3zf9SFlcD0yJiB+Aw4PLM/E1mPgBcztOTkSSpj/p9AuD2mXk3QGbeHRHPrPNnAHe2LbeizlvTfEmT1KyTL+3q+n552hFdXd9kMFE6x2OUebmW+U9fQcTCiFgaEUtXrlzZ1cJJklbrd+K4tzZBUf/eV+evAHZsW24mcNda5j9NZi7OzLmZOXf69HVeakWSNEb9ThyXAK2RUfOBi9vmn1BHV+0PPFibtL4DHBoRW9dO8UPrPEnSgPSsjyMizgMOAraLiBWU0VGnARdExALgDuCYuvhlwOHAcuAR4ESAzPxNRHwU+HFd7tTMHNnhLknqo54ljsw8dg0vHTzKsgm8cw3rORs4u4tFkySNw0TpHJckDQkThySpEROHJKkRE4ckqREThySpkfXynuOS+q/blwIBLwcyUVnjkCQ1YuKQJDVi4pAkNWLikCQ1YuKQJDVi4pAkNWLikCQ1YuKQJDVi4pAkNWLikCQ14iVHpAHxEh0aViYOaT1nglK32VQlSWrEGoc0im4fpXuErvWJNQ5JUiMmDklSIyYOSVIjJg5JUiMmDklSIyYOSVIjJg5JUiMmDklSIyYOSVIjJg5JUiMmDklSIyYOSVIjA0kcEfG+iLglIm6OiPMiYpOI2CUiromI2yLiqxGxUV124zq9vL4+axBlliQVfU8cETEDeDcwNzN3B6YAbwQ+DpyembOBB4AF9S0LgAcyc1fg9LqcJGlABnVZ9anAphHxBPAM4G7glcBx9fUlwEeAM4Gj6nOAC4F/jIjIzOxngTUxeLlzafD6XuPIzP8EPgncQUkYDwLXAr/NzFV1sRXAjPp8BnBnfe+quvy2/SyzJGm1QTRVbU2pRewCPBvYDJg3yqKtGkWs5bX29S6MiKURsXTlypXdKq4kaYRBdI6/CvhFZq7MzCeAi4CXAtMiotV0NhO4qz5fAewIUF/fCvjNyJVm5uLMnJuZc6dPn97rbZCkSWsQieMOYP+IeEZEBHAwcCtwJfCGusx84OL6/JI6TX39u/ZvSNLgDKKP4xpKJ/d1wE21DIuBDwGLImI5pQ/jrPqWs4Bt6/xFwMn9LrMkabWBjKrKzFOAU0bMvh3Yd5RlHwWO6Ue5NHbdHu0EjniSJirPHJckNTKo8zgkacKyBr121jgkSY2YOCRJjZg4JEmNmDgkSY2YOCRJjZg4JEmNmDgkSY2YOCRJjZg4JEmNmDgkSY2YOCRJjXitqlF4X2tJWjMTx3rOi7VJ6jabqiRJjZg4JEmNmDgkSY2YOCRJjZg4JEmNmDgkSY2YOCRJjZg4JEmNmDgkSY2YOCRJjZg4JEmNmDgkSY2YOCRJjZg4JEmNmDgkSY2YOCRJjZg4JEmNDCRxRMS0iLgwIn4aEcsi4iURsU1EXB4Rt9W/W9dlIyI+GxHLI+LGiJgziDJLkopB1TjOAL6dmbsBewLLgJOBKzJzNnBFnQaYB8yuj4XAmf0vriSpZZ33HI+IKcB3MvNV3QgYEVsCLwPeApCZjwOPR8RRwEF1sSXAVcCHgKOAL2VmAlfX2soOmXl3N8ozKN4LXNKwWmeNIzP/ADwSEVt1KeZzgJXAORFxfUR8ISI2A7ZvJYP695l1+RnAnW3vX1HnSZIGYJ01jupR4KaIuBz4fWtmZr57jDHnAO/KzGsi4gxWN0uNJkaZl09bKGIhpSmLnXbaaQzFkiR1otPEcWl9dMMKYEVmXlOnL6QkjntbTVARsQNwX9vyO7a9fyZw18iVZuZiYDHA3Llzn5ZYJEnd0VHiyMwlEbEpsFNm/mw8ATPznoi4MyKeX9d1MHBrfcwHTqt/L65vuQQ4KSLOB/YDHhz2/g1JGmYdJY6IOBL4JLARsEtE7AWcmpmvHWPcdwHnRsRGwO3AiZT+lgsiYgFwB3BMXfYy4HBgOfBIXVaSht6wDpLptKnqI8C+lJFOZOYNEbHLWINm5g3A3FFeOniUZRN451hjSZK6q9PzOFZl5oMj5tmPIEmTUKc1jpsj4jhgSkTMBt4N/LB3xZIkTVSd1jjeBbwQeAw4D3gIeG+vCiVJmrg6HVX1CPDhiPh4mcyHe1ssSdJE1VGNIyL2iYibgBspJwL+JCJe3NuiSZImok77OM4C3pGZ/w4QEQcC5wB79KpgkqSJqdM+jodbSQMgM78P2FwlSZPQWmscbfe++FFEfJ7SMZ7Af6ee0yFJmlzW1VT1qRHTp7Q99zwOSZqE1po4MvMV/SqIJGk4dHqtqmnACcCs9veM8bLqkqQh1umoqsuAq4GbgCd7VxxJ0kTXaeLYJDMX9bQkkqSh0Olw3C9HxNsiYoeI2Kb16GnJJEkTUqc1jseBTwAfZvVoqqTcP1ySNIl0mjgWAbtm5v29LIwkaeLrtKnqFsrd9yRJk1ynNY4/ADdExJWUS6sDDseVpMmo08Tx9fqQJE1ynd6PY0mvCyJJGg6dnjn+C0a5NlVmOqpKkiaZTpuq5rY93wQ4BvA8DkmahDoaVZWZv257/GdmfgZ4ZY/LJkmagDptqprTNrkBpQayRU9KJEma0DptqvoUq/s4VgG/pDRXSZImmU4Txzzg9Tz1supvBE7tQZkkSRNYk/M4fgtcBzzau+JIkia6ThPHzMx8dU9LIkkaCp1eq+qHEfGinpZEkjQUOq1xHAi8pZ4I+BgQQGbmHj0rmSRpQmrSOS5JUsfXqvpVrwsiSRoOnfZxSJIEDDBxRMSUiLg+Ir5Zp3eJiGsi4raI+GpEbFTnb1ynl9fXZw2qzJKkwdY43gMsa5v+OHB6Zs4GHgAW1PkLgAcyc1fg9LqcJGlABpI4ImImcATwhTodlIsmXlgXWQIcXZ8fVaeprx9cl5ckDcCgahyfAT4IPFmntwV+m5mr6vQKYEZ9PgO4E6C+/mBd/ikiYmFELI2IpStXruxl2SVpUut74oiI1wD3Zea17bNHWTQ7eG31jMzFmTk3M+dOnz69CyWVJI2m0/M4uukA4LURcTjlplBbUmog0yJiaq1VzATuqsuvAHYEVkTEVGAr4Df9L7YkCQZQ48jMv8rMmZk5i3KF3e9m5puAK4E31MXmAxfX55fUaerr383Mp9U4JEn9MZHO4/gQsCgillP6MM6q888Ctq3zFwEnD6h8kiQG01T1R5l5FXBVfX47sO8oyzyKN42SpAljItU4JElDwMQhSWrExCFJasTEIUlqxMQhSWrExCFJasTEIUlqxMQhSWrExCFJasTEIUlqxMQhSWrExCFJasTEIUlqxMQhSWrExCFJasTEIUlqxMQhSWrExCFJasTEIUlqxMQhSWrExCFJasTEIUlqxMQhSWrExCFJasTEIUlqxMQhSWrExCFJasTEIUlqxMQhSWrExCFJasTEIUlqxMQhSWqk74kjInaMiCsjYllE3BIR76nzt4mIyyPitvp36zo/IuKzEbE8Im6MiDn9LrMkabVB1DhWAe/PzD8B9gfeGREvAE4GrsjM2cAVdRpgHjC7PhYCZ/a/yJKklr4njsy8OzOvq88fBpYBM4CjgCV1sSXA0fX5UcCXsrgamBYRO/S52JKkaqB9HBExC9gbuAbYPjPvhpJcgGfWxWYAd7a9bUWdJ0kagIEljojYHPga8N7MfGhti44yL0dZ38KIWBoRS1euXNmtYkqSRhhI4oiIDSlJ49zMvKjOvrfVBFX/3lfnrwB2bHv7TOCukevMzMWZOTcz506fPr13hZekSW4Qo6oCOAtYlpmfbnvpEmB+fT4fuLht/gl1dNX+wIOtJi1JUv9NHUDMA4A3AzdFxA113l8DpwEXRMQC4A7gmPraZcDhwHLgEeDE/hZXktSu74kjM7/P6P0WAAePsnwC7+xpoSRJHfPMcUlSIyYOSVIjJg5JUiMmDklSIyYOSVIjJg5JUiMmDklSIyYOSVIjJg5JUiMmDklSIyYOSVIjJg5JUiMmDklSIyYOSVIjJg5JUiMmDklSIyYOSVIjJg5JUiMmDklSIyYOSVIjJg5JUiMmDklSIyYOSVIjJg5JUiMmDklSIyYOSVIjJg5JUiMmDklSIyYOSVIjJg5JUiMmDklSIyYOSVIjQ5M4IuLVEfGziFgeEScPujySNFkNReKIiCnAPwHzgBcAx0bECwZbKkmanIYicQD7Assz8/bMfBw4HzhqwGWSpElpWBLHDODOtukVdZ4kqc8iMwddhnWKiGOAwzLzf9TpNwP7Zua72pZZCCysk88HftaHom0H3L+exFmftsU4EzeGcSZ2nJ0zc/q6Fpra40J0ywpgx7bpmcBd7Qtk5mJgcT8LFRFLM3Pu+hBnfdoW40zcGMaZ+HE6MSxNVT8GZkfELhGxEfBG4JIBl0mSJqWhqHFk5qqIOAn4DjAFODszbxlwsSRpUhqKxAGQmZcBlw26HCP0q2msH3HWp20xzsSNYZyJH2edhqJzXJI0cQxLH4ckaYIwcUiSGjFxSJIaGZrO8YksIjbIzCd7tO59gQ2BVZl5TS9i1Dh7AY8BZOayYY1R4/TrM1vf4vRjH+jLtrTFmw5smJl3tc2LtHN3XKxxjEFEHBERfxcRH4uIbXuYNA6jnK9yBHBeRJwUEZv3IM484BvAO4B/jYgThzFGjdOvz2x9i9OPfaAv29IW7w3ApcDFEfHRiPhvAJmZERFdjrVzRDxvxLyuxuhnnHXKTB8NHsB+wC+A44B/Bn4AvJRyVNOtGAFsDHwR+LM6by/gcuADwKZdjLM5ZZjza+u8/YHlwJ8PS4wBfGbrW5x+7AM935YRMbcFvg/sCTwL+Hvgk8Cf9iDWG4DrgauBTwPHt2/7sMXp5GGNo7ndgX/LzK9k5p8DXwM+CMyB0mw13gBZPAYsA/aIiM0z8wbgvcDhwFvHG6Mtzu+ApcCWEbFhZl5NOTP/QxExfxhitMXp12e2vsXpxz7Q820ZYQolWT2amfcApwN3AC+JiP27FSQiNgP+Angb8ErgFmD/iHgvlG0fpjidMnE092Ng04jYDSAzP005svlMREzL7jZb3Ug5cnpuREzNcrb8XwKLImLPLsa5BzgY2BQgM5cCbwbeFRG7DFEM6PFn1tYs0Os4rf/N9Wkf6Ne2kJn3UQ7qFkTEszPz18BX6suHdzFUUPpspmTmI8AFlCtcPCci/mwI43TExNHcPcAq4JCI2A4gMz8J3Ay8vZuBMvNbwO+A9wC71yO1a4FvU3akbsX5HPAM4J8jYqt61Pl9yj/6uI5k6k24ehqjxokapyefWT3i++ORXQ/jzIyIjVoHIOvJPtDT72Ytrqx/31iTx/3AGcArWv+741Vra+cDfxkRz83Mh4H/B/yU0qzdFf2K0ynPHO9AREzJzD+0Te9NaTP9NnBVZt4U5Xa2T2bmP4wxxq7ANODmzHx0xGv/AGwBPEq5L8n7gQMy85djiPNCyuWZl2Xmfe0jTCLifOC/KG2oU4FFwMszc0XDGAcCu2Tml+v0RlluwNW1GHVdRwLPycwz6vQfR7d1+TM7CjgEOLV+Zr2KcxjwEeBNmXl7/fF+ogdx9gdmA7cB17W+m/pat/aB2cBWlDZ5Rvz/dG1b1hD7j/+vEXEE8HJKs9Viyh1E3wO8uv4YjydOZGZGxA6UJqTtgDMy8+cRsQXl9+G4zPzVMMRpJPvYoTJsD+B5bc+ntH5f69+9KZ3j5wPnAT8HXjTGOK+hHNldWde1e52/Ydsyr6Ds8P8EvGCMcebVOF+njDaZMUqctwL/E/hX4IUN178BpaP1FuBW2jpXgU26EaNtHYcCNwCHjJg/pcuf2cspR3WHrGWZbsRpbc8vKT8Kvdqe19Z9YAlwITC79d116/sBjgZ+QmkqOoMyUmuzbn9mbevar35P+7TNa9+n51A64b8HXAHMGU+8NZRhT+BvKaPGDgCOBX4EbNOFdW/QjziNytTPYMP0qD/mjwBfaZvXSh4b1L/bUY7cjqMcYY8lzkvrD9PedfpzlKv/Pm2nqdNTxxjnIOA/KDfAAvi/wKvat2vE8huP47P7IOUo8kvA+9ay3Jhi1M/s3rZt2QrYGdhstM9nrJ9Zfe8i4AP1+bMpNY/9gGndigO8ijKK6YWUdux/A17Wg31gW0q7eOvA5GzgGOCZwDO68f3UGN9qJQNKEvoxJRFt2c3vpr5/HqXmtJhyQHTWmspf/183G0esp5V/xOvTKc3V36QcmI0pQVES3YGt/XvkPtCtOON52McxitqefRJl1MfjEfEvUKrbtVOv1QG+KjNvyzLC6hfjCHlaZl5fn58CbBMRG9eYT0bEPhHxmvr6H0Zdw7rdC7w9M38UEc+i/PidFBGfp3SCEhEvjog5dfnH17CeTqyi3HhrCbBvRHw6Ij5WY7y0CzF+DTwB7BAR21J+MM4EzmH1tuxTmylg7J8ZlG1puZDyQ3gS8I8RsWFE7NWFOFOAE7J0Fm9GuXvlC+GPzRRPRsS+XYizitL5vVtEbEk5mDiBMuLor2u8vcf5/ayi1DqfBZCZZwO/ovzYHVFjvKQb303tP5tPaUJcSNmW50fEhTX2Y3W5AyNi48y8PzN/P8ZYfwr8e0TsFyNGTramM3NlZn6eMiLtdZl53RjivAY4i3I30w9ExNvrup9s6ysad5zxMnGMou5cb6WMwvgAsElb8lgFUEeBHB8Rm7SNtBmLa4CL6jpbQwh3Bras82YCu1GGS5L1kGMM27QsM1udhQuAz2Xm0ZS27MMjYhbwMuqdFccap7oYuCczr6jl/gtK/w3APuONkZk/o/wInU5pEvkKpYb4beCwiJgB7AJcN5441XeBt9W2//+TmcdSkvvvgcOA5403TmZ+JzN/WPtOfks5ijwlIl6UmRkRGwLP7UKcB4HPAn9FqdWck5lHUn6odoqIVpPPmL+fGuNc4MSIeHNE/C9KX8atlOY4KPv3uL+bLP0Y17dNP5SZBwLb1wOi1kHgQZTaxpjU/41FwH3A+4A57f/zubq/67CI2CQzf5dt/UYN4uwN/G/gLZl5AqWpcLe2OK2+yHHF6Yp+V3GG8UGpfn8N+Jc6vQfweuCZXY4zlXK0dkWdPh74FLBFj7fvW7T153Rhfc+mHP2/jdKM8LeUavVxdPeEqBcA7xwx79vA87v8+RxJOenz1LZ5XwBe38Pv5FTKD3yrWbSbn9vWwCeA17TNuwh4RZfWvxXwproPnN42/9K6j49rW3hq3+PxlBGNO7XN245SO/wTymitjcYZbyfKAAFY3b8wl9rMxup+z78DZo0jzkt5ar/grpT+ix3bPzPKAIoxx+nKdzzI4MP0qDvjOZRmhNuAHXoY64vAx4BrgT26vO4YMf36Gqer21N/+O4AjqzTrwB27PF31NqWZ3V5vVMpzSC3U2prCyg1qV17vC3fZ5x9AGtZ/7y6Px9K6TC/rts/Rjy1Xf4E4IfA5uNcZ6vv8fy2eR+ljM5qTx7nA/uPM1Z7gtqq7fnfUC7Psk+dHtOgmDXEmV7/TqEMj/4GtW+FOpBhIjwGXoBhelCqqfeMd0dZy/oD2IgyQuuOXu4olCaxBZQRULv3YP07Ai9um96g2zFGfG5vpTSHjGmUVodx5lCaEj7Vq31gRLwLenVkSWk6fDflXIDvAHv2cDta3814f2A3o9QoF9aDq/PaXvsopdny7cCHKWepj2nASl1fK0G1x9io7fnfUJpITwNuYoytD2tIhK1a5gaUS8FsSem7+wawda/3u04ensfRoYjYmvKP/P7MvLHHsd4C/Dh7eF/12m5+CPDzLH0GvYrT8yuR1vbml1P6VX7ay1j90I/PrC3WFpRa6EM9jLEzZXjs8i6s69nAQ8AmlOHwT2TpdyIiXkfplH8x8JnMvHmMMTajNE1fRGk+mpqZx9fXNs7Vne5XUfq4DsvMm7ocZwrlgOg84EHKtb1OyMxbx7JN3WbiaKB2SD267iXHHcfLPkvrUEfULQYez8xj68mtv8sunAg3SoJ6tPWjXl9/HvBVSkf2T3oY5+uU5PS6Xh7gNWXikDS0olw65BOUI/YpwEE5hqsQrCNGK0H9V2YeH+W+JVsCt2a5jEmv4swGTqQMypkQNY0Wh+NKGlr1h/tGykiu13U7adQYv6b0nTwRET+lDJNd3s2kMUqc/6CMQjtjoiUNMHFIGmK17/Fw4NCx9DN0qi1BTaPc0+OudbxlvHG2pAz3vrcXccbLW8dKGlqZ+UBEHNnrvsd+Jah+xRkv+zgkqQN9HBzTlzjjYeKQJDViH4ckqREThySpEROHJKkRE4c0AUTEtIh4R9v0QRHxzUGWSVoTE4c0MUyj3GJVmvBMHFJDETErIn4aEV+IiJsj4tyIeFVE/CAibqt369smIr4eETdGxNURsUd970ci4uyIuCoibo+Id9fVngY8NyJuiIhP1HmbR8SFNda547xhmNQ1ngAojc2ulPt1L6TcU/s4yn2iX0u5DeudwPWZeXREvJJy//W96nt3o9yfZAvgZxFxJnAy5fL2e0FpqgL2ptxC9i7gB8ABlHt0SANljUMam19k5k1Zbht6C+WujUm5N8MsShL5MkBmfhfYNiK2qu+9NDMfq5eXuA/Yfg0xfpSZK2qMG+p6pYEzcUhj81jb8yfbpp+k3h51lPe0zrZtf+8fWHPNv9PlpL4ycUi98T3KfbdbzU73r+NmSQ9Tmq6kCc8jGKk3PgKcExE3Um4NOn9tC2fmr2vn+s3AtyiX1JYmJK9VJUlqxKYqSVIjJg5JUiMmDklSIyYOSVIjJg5JUiMmDklSIyYOSVIjJg5JUiP/Hw9vWHkNelvoAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "## 绘制日均租车量-时间变化直方图（在sql中仅需输出month与number）\n",
    "query = \"\"\"\n",
    "select extract(month from date) as month ,avg(count) as number from (\n",
    "    select extract(doy from start_time),count(*) from trip \n",
    "    where start_station_id in (select station_id from station where zip_code = '94107') \n",
    "        and extract(year from start_time) = 2014\n",
    "    group by extract(doy from start_time)\n",
    ") as days, weather where extract(doy from date) = date_part \n",
    "group by  extract(month from date) order by month;\n",
    "\"\"\"\n",
    "result = %sql $query\n",
    "%matplotlib inline\n",
    "result.bar()"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "发现的规律是平均气温升高，租车量也会相应上升；\n",
    "一月份的租车量也是一个小峰值。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.4.8 分析天气与租车之间的关联关系 (5分)\n",
    "\n",
    "查询不同天气下总的租车数量。查询结果模式为(events, number)，events为None是指没有事件发生，events字符串需要使用[string函数](https://www.postgresql.org/docs/current/static/functions-string.html)全部转成小写，number为某一events下的总租车数量，仅考虑租车（非还车）时的天气，不同区域的天气可能不同。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>events</th>\n",
       "        <th>number</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>rain</td>\n",
       "        <td>282395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>2958504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>rain-thunderstorm</td>\n",
       "        <td>1750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>fog-rain</td>\n",
       "        <td>11672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>fog</td>\n",
       "        <td>95474</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('rain', Decimal('282395')),\n",
       " (None, Decimal('2958504')),\n",
       " ('rain-thunderstorm', Decimal('1750')),\n",
       " ('fog-rain', Decimal('11672')),\n",
       " ('fog', Decimal('95474'))]"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select lower(events) as events,sum(counts.count) as number from weather,\n",
    "(select date(start_time) ,count(*)\n",
    "from trip group by date(start_time)) as counts\n",
    "where weather.date = counts.date\n",
    "group by lower(events);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "从查询结果来看，是否可以得出如下结论：\n",
    "\n",
    "    * 当天气为rain-thunderstorm时，选择租车的可能性最小？\n",
    "    * 在rain时选择租车的可能性大于在fog时选择租车的可能性？\n",
    "    \n",
    "上述查询是否支持上述结论，请说明理由，或构造新的查询，进一步分析天气与租车之间的关联关系"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "从上述查询来看\n",
    "当天气为rain-thunderstorm时，选择租车的可能性最小；在rain时选择租车的可能性大于在fog时选择租车的可能性。\n",
    "上述查询只考虑了天气和日期之间的关系，当不同区域存在不同天气时存在对租车次数的重复计算，因此可能不准确；但能大致反映出天气与租车之间的关联关系"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>events</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>fog</td>\n",
       "        <td>43676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>fog-rain</td>\n",
       "        <td>6877</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>rain</td>\n",
       "        <td>71613</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>rain-thunderstorm</td>\n",
       "        <td>1475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>546318</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('fog', 43676),\n",
       " ('fog-rain', 6877),\n",
       " ('rain', 71613),\n",
       " ('rain-thunderstorm', 1475),\n",
       " (None, 546318)]"
      ]
     },
     "execution_count": 185,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select lower(events) as events, count(*) from weather,\n",
    "\t(select * from trip,station \n",
    "\twhere start_station_id = station_id) as trips\n",
    "where weather.zip_code = trips.zip_code \n",
    "\tand date(trips.start_time) = weather. date\n",
    "group by lower(events);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "添加地区考虑的计数显示了类似结果。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.4.9 (附加题) 查询到达过San Jose所有站点的自行车，一次租车记录，自行车既到达了租车站点，又到达了还车站点。查询返回这类自行车数目。(4分)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "查询思路描述：\n",
    "获取在San Jose城市中的站点：\n",
    "select * from station where city = 'San Jose'\n",
    "查找起始站点或终点站在San Jose城市中的站点的trip，并按照bike_id分类： \n",
    "with cityStations as\n",
    "        (select * from station where city = 'San Jose')\n",
    "    select bike_id from trip \n",
    "        where \n",
    "        start_station_id in (select station_id from cityStations) \n",
    "        or end_station_id in (select station_id from cityStations)\n",
    "    group by bike_id\n",
    "最后统计总类别数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/hw1\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>333</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(333,)]"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select count(*) from (\n",
    "    with cityStations as\n",
    "        (select * from station where city = 'San Jose')\n",
    "    select bike_id from trip \n",
    "        where \n",
    "        start_station_id in (select station_id from cityStations) \n",
    "        or end_station_id in (select station_id from cityStations)\n",
    "    group by bike_id\n",
    ") as sumBikes;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 作业感想\n",
    "\n",
    "收获:-)，疑惑:-|，吐槽:-(，...，你的反馈很重要"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "（讲完第一节课就做了...可能还有很多sql写的不规范的地方...要看看如何写出规范优美的查询x）\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
